package www.tshu.dao.impl;

import www.tshu.dao.UserDao;
import www.tshu.pojo.Grade;
import www.tshu.pojo.PI;
import www.tshu.pojo.User;
import www.tshu.util.PageUtil;
import www.tshu.util.TuShuUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {

    public User login(User user) throws SQLException {
        Connection conn = TuShuUtil.getConnection();
        String sql="select*from user where unum=?";
        PreparedStatement pr =conn.prepareStatement(sql);
        pr.setString(1,user.getUnum());
        ResultSet rs = pr.executeQuery();
        while (rs.next()){
            int uid = rs.getInt("uid");
            String unum = rs.getString("unum");
            String uname = rs.getString("uname");
            String upwd = rs.getString("upwd");
            int gid = rs.getInt("gid");
            Grade grade = new Grade(gid,null);
            User user1 = new User(uid,unum,uname,upwd,grade);
            conn.close();
            return user1;
        }
      return null;
    }

    public User login1(User user) throws SQLException{
        Connection conn = TuShuUtil.getConnection();
        String sql="select*from user where unum=?";
        PreparedStatement pr =conn.prepareStatement(sql);
        pr.setString(1,user.getUnum());
        ResultSet rs = pr.executeQuery();
        while (rs.next()){
            String unum = rs.getString("unum");
            String uname = rs.getString("uname");
            String upwd = rs.getString("upwd");
            User user1 = new User(unum,uname,upwd);
            return user1;
        }
        return null;
    }

    public List<PI> findUser(String unum) throws SQLException{
        Connection conn = TuShuUtil.getConnection();
        String sql ="select*from user u,pi p where u.uid=p.uid and u.unum=?";
        PreparedStatement pr =conn.prepareStatement(sql);
        pr.setString(1,unum);
        ResultSet rs = pr.executeQuery();
        List<PI>plist = new ArrayList<>();
        while (rs.next()){
            int uid = rs.getInt("uid");
            String unum1 =rs.getString("unum");
            String uname = rs.getString("uname");
            User user = new User(uid,unum1,uname);
            String phone = rs.getString("phone");
            int sex = rs.getInt("sex");
            int age = rs.getInt("age");
            Double money = rs.getDouble("money");
            String email = rs.getString("email");
            PI pi = new PI(user,phone,sex,age,money,email);
           plist.add(pi);
        }
        return plist;
    }

    public int editUser(PI pi) throws SQLException{
        Connection conn = TuShuUtil.getConnection();
        String sql = "update user set uname=? where uid=?";
        PreparedStatement pr = conn.prepareStatement(sql);
        pr.setString(1,pi.getUser().getUname());
        pr.setInt(2,pi.getUser().getUid());
        int row = pr.executeUpdate();
        return row;
    }

    public int editPi(PI pi) throws SQLException{
        Connection conn = TuShuUtil.getConnection();
        String sql = "update pi set phone=?,sex=?,age=?,email=? where uid=?";
        PreparedStatement pr = conn.prepareStatement(sql);
        pr.setString(1,pi.getPhone());
        pr.setInt(2,pi.getSex());
        pr.setInt(3,pi.getAge());
        pr.setString(4,pi.getEmail());
        pr.setInt(5,pi.getUser().getUid());
        int row1 = pr.executeUpdate();
        return row1;
    }

    @Override
    public int addUser(PI pi) throws SQLException {
        Connection conn = TuShuUtil.getConnection();
        String sql = "insert into user(unum,uname,upwd)values(?,?,?)";
        PreparedStatement pr = conn.prepareStatement(sql);
        pr.setString(1,pi.getUser().getUnum());
        pr.setString(2,pi.getUser().getUname());
        pr.setString(3,pi.getUser().getUpwd());
        int row = pr.executeUpdate();
        return row;
    }

    @Override
    public int addPi(PI pi) throws SQLException {
        Connection conn = TuShuUtil.getConnection();
        String sql = "insert into pi(uid,phone,sex,age,email)values((select uid from user where unum=?),?,?,?,?)";
        PreparedStatement pr = conn.prepareStatement(sql);
        pr.setString(1,pi.getUser().getUnum());
        pr.setString(2,pi.getPhone());
        pr.setInt(3,pi.getSex());
        pr.setInt(4,pi.getAge());
        pr.setString(5,pi.getEmail());
        int row =pr.executeUpdate();
        return row;
    }

    @Override
    public int editPwd(User user) throws SQLException {
        Connection conn = TuShuUtil.getConnection();
        String sql ="update user set upwd=? where unum=?";
        PreparedStatement pr =conn.prepareStatement(sql);
        pr.setString(1,user.getUpwd());
        pr.setString(2,user.getUnum());
        int row =pr.executeUpdate();
        return row;
    }

    @Override
    public int getUserListCount(User user) throws SQLException {
        Connection conn = TuShuUtil.getConnection();
        PreparedStatement pr =null;
        if (user.getUname()!=null){
            String sql="select count(uid) from user where uname like ? and gid =1";
            pr = conn.prepareStatement(sql);
            pr.setString(1,"%"+user.getUname()+"%");
        }else {
            String sql="select count(uid) from user where gid =1";
            pr = conn.prepareStatement(sql);
        }
        ResultSet rs = pr.executeQuery();
        while (rs.next()){
            return rs.getInt(1);
        }
        return 0;
    }

    @Override
    public List<PI> userList(User user, PageUtil pageUtil) throws SQLException {
        Connection conn = TuShuUtil.getConnection();
        PreparedStatement pr =null;
        if (user.getUname()!=null){
            String sql ="select *from user u ,pi p where u.uid=p.uid and u.uname like ? and gid=1 order by u.uid limit ?,?";
            pr = conn.prepareStatement(sql);
            pr.setString(1,"%"+user.getUname()+"%");
            pr.setInt(2, pageUtil.getStartIndex());
            pr.setInt(3, pageUtil.getPageSize());
        }else {
            String sql ="select *from user u ,pi p where u.uid=p.uid  and gid=1 order by u.uid limit ?,?";
            pr = conn.prepareStatement(sql);
            pr.setInt(1, pageUtil.getStartIndex());
            pr.setInt(2, pageUtil.getPageSize());
        }
        ResultSet rs = pr.executeQuery();
        List<PI>plist = new ArrayList<>();
        while (rs.next()){
            int uid =rs.getInt("uid");
            String unum = rs.getString("unum");
            String uname =rs.getString("uname");
            String upwd = rs.getString("upwd");
            User user1 =new User(uid,unum,uname,upwd,null);
            String phone = rs.getString("phone");
            int sex = rs.getInt("sex");
            int age = rs.getInt("age");
            Double money = rs.getDouble("money");
            String email = rs.getString("email");
            PI pi =new PI(user1,phone,sex,age,money,email);
            plist.add(pi);
        }
        return plist;
    }

    @Override
    public int delUser(String uid) throws SQLException{
        Connection conn = TuShuUtil.getConnection();
       String sql= "delete from user where uid =?";
       PreparedStatement pr = conn.prepareStatement(sql);
       pr.setInt(1,Integer.valueOf(uid));
       int row = pr.executeUpdate();
        return row;
    }

    @Override
    public int delPi(String uid) throws SQLException{
        Connection conn = TuShuUtil.getConnection();
        String sql= "delete from pi where uid =?";
        PreparedStatement pr = conn.prepareStatement(sql);
        pr.setInt(1,Integer.valueOf(uid));
        int row = pr.executeUpdate();
        return row;

    }

    @Override
    public int delUb(String uid) throws SQLException{
        Connection conn = TuShuUtil.getConnection();
        String sql= "delete from ub where uid =?";
        PreparedStatement pr = conn.prepareStatement(sql);
        pr.setInt(1,Integer.valueOf(uid));
        int row = pr.executeUpdate();
        return row;
    }

}
